package org.tarena.dang.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.tarena.dang.entity.Category;
import org.tarena.dang.util.DbUtil;

public class JdbcCategoryDAO implements CategoryDAO{
	private static final String findAll = "select * from d_category order by turn";
	private static final String findByParentId =	
	"select dc.*, "+
	"	count(dcp.product_id) as pnum "+
	"from d_category dc  "+
	"	left outer join d_category_product dcp  "+
	"	on(dc.id=dcp.cat_id) "+
	"where dc.parent_id=? "+
	"group by dc.id "+
	"order by dc.turn ";


	public List<Category> findAll() throws Exception {
		Connection conn = DbUtil.getConnection();
		PreparedStatement stat = conn.prepareStatement(findAll);
		ResultSet rs = stat.executeQuery();
		List<Category> list = new ArrayList<Category>();
		while(rs.next()){
			Category cat = new Category();
			cat.setId(rs.getInt("id"));
			cat.setName(rs.getString("name"));
			cat.setEnName(rs.getString("en_name"));
			cat.setTurn(rs.getInt("turn"));
			cat.setDescription(rs.getString("description"));
			cat.setParentId(rs.getInt("parent_id"));
			list.add(cat);
		}
		DbUtil.closeConnection();
		return list;
	}
	public List<Category> findByParentId(int pid) throws Exception {
		Connection conn = DbUtil.getConnection();
		PreparedStatement stat = conn.prepareStatement(findByParentId);
		stat.setInt(1,pid);
		ResultSet rs = stat.executeQuery();
		List<Category> list = new ArrayList<Category>();
		while(rs.next()){
			Category cat = new Category();
			cat.setId(rs.getInt("id"));
			cat.setName(rs.getString("name"));
			cat.setEnName(rs.getString("en_name"));
			cat.setTurn(rs.getInt("turn"));
			cat.setDescription(rs.getString("description"));
			cat.setParentId(rs.getInt("parent_id"));
			cat.setPnum(rs.getInt("pnum"));
			list.add(cat);
		}
		return list;
	}
}
